# -*- coding: UTF-8 -*-
# Author: Damon(wuud1)
# CreateDate: 
# Message:
from base_class import BaseClass
import os
import redis
import json
from my_utils import clean

class GetCarsStructureCollection(BaseClass):

    def __init__(self, **kwargs):
        self.src_db = kwargs.get("src_db")
        self.dest_db = kwargs.get("dest_db")
        self.insert_table = kwargs.get("insert_table")
        self.src_conn, self.src_curs = self.get_conn_curs(db_config=self.src_db, type='dict')
        self.dest_conn, self.dest_curs = self.get_conn_curs(db_config=self.dest_db, type='dict')
        self.num = 0
        self.num_label = {
            "ENGINE MECHANICAL": "01",
            "FUEL & ENGINE CONTROL": "02",
            "EXHAUST & COOLING": "03",
            "ENGINE ELECTRICAL": "04",
            "BODY ELECTRICAL": "05",
            "POWER TRAIN": "06",
            "AXLE & SUSPENSION": "07",
            "BRAKE": "08",
            "STEERING": "09",
            "BODY(FRONT,ROOF & FLOOR)": "10",
            "BODY(SIDE & REAR)": "11",
            "SEAT & SEAT BELT": "12",
            "MISCELLANEOUS": "13",
            "BODY(BACK DOOR & REAR BODY)": "14",
        }
        self.cn = {
            "ENGINE MECHANICAL": "发动机",
            "FUEL & ENGINE CONTROL": "燃料与发动机控制",
            "EXHAUST & COOLING": "排气和冷却",
            "ENGINE ELECTRICAL": "发动机电气",
            "BODY ELECTRICAL": "车身电气",
            "POWER TRAIN": "传动系",
            "AXLE & SUSPENSION": "轴与悬挂",
            "BRAKE": "制动",
            "STEERING": "转向装置",
            "BODY(FRONT,ROOF & FLOOR)": "车身（前、顶、地板）",
            "BODY(SIDE & REAR)": "车身（侧、后）",
            "SEAT & SEAT BELT": "座椅和安全带",
            "MISCELLANEOUS": "杂项",
            "BODY(BACK DOOR & REAR BODY)": "车身（后门和后部）",
        }
        self.r = redis.Redis(host='localhost', port=6379, db=0).pipeline()  # redis链接,用于断点续爬

    def get_cid_list(self):
        # sql = '''select distinct code from cars_info_collection'''
        # self.src_curs.execute(sql)
        # data = self.src_curs.fetchall()
        # return [i.get("code") for i in data]

        # sql = '''select distinct f2 from destcnt'''
        # self.src_curs.execute(sql)
        # data = self.src_curs.fetchall()
        # return [i.get("f2") for i in data]
        sql = '''select distinct code from cars_info_collection'''
        self.src_curs.execute(sql)
        data = self.src_curs.fetchall()
        return [i.get("code") for i in data]

    def get_main_group_dict(self, cid):
        sql = '''select * from genloc where trim(f2)="{}";'''.format(cid.strip())
        self.src_curs.execute(sql)
        data = self.src_curs.fetchall()
        return {i.get("f3"):i.get("f4") for i in data}

    def get_kid_group_info(self):
        sql = '''select distinct * from gsecloc_cn'''
        self.src_curs.execute(sql)
        data = self.src_curs.fetchall()
        cid_group_code_list = [i.get("f2").strip()+i.get("f3").strip() for i in data]
        kid_group_info = {i:[] for i in cid_group_code_list}
        for i in data:
            cid_group_code = i.get("f2").strip()+i.get("f3").strip()
            _dict = {"kid_group_code":i.get("f4"), "kid_group_name":i.get("f5"), "kid_group_name_cn":i.get("TEXT_CN")}
            kid_group_info[cid_group_code].append(_dict)
        return kid_group_info

    def get_kid_group_info_(self, cid, group_code):
        sql = '''select distinct * from gsecloc_cn t where trim(f2)='{}' and trim(f3)='{}';'''.format(cid.strip(), group_code.strip())
        self.src_curs.execute(sql)
        data = self.src_curs.fetchall()
        return [{"kid_group_code":i.get("f4"), "kid_group_name":i.get("f5"), "kid_group_name_cn":i.get("TEXT_CN")} for i in data]

    # def get_mid(self, cid, kid_group_code):
    #     sql = '''select distinct F4,F9 from illnote t where trim(f2)="{}" and trim(f3)="{}"'''.format(cid.strip(), kid_group_code.strip())
    #     self.src_curs.execute(sql)
    #     data = self.src_curs.fetchall()
    #     return [{"img_group":i.get("F4"), "img_code": i.get("F9")} for i in data]

    def get_img_list(self, path):
        return os.listdir(path)

    def get_mid_new(self):
        sql = u'''select distinct * from `illnote`'''
        self.src_curs.execute(sql)
        data = self.src_curs.fetchall()
        cid_kid_list = [i.get("f2").strip()+i.get("f3").strip() for i in data]
        img_info = {i: {} for i in cid_kid_list}
        # {cid_kid:{kid_A01:{'extra_info':[], 'img': []}, kid_A02:{'extra_info':[], 'img': []}, }, cid_kid:{}}
        for i in data:
            cid_kid = i.get("f2").strip()+i.get("f3").strip()
            kid_A = i.get("f3").strip()+i.get("f4").strip()
            if kid_A not in img_info[cid_kid]:
                img_info[cid_kid][kid_A] = {'extra_info':[], 'img': []}
            img = i.get("f9").strip()

            _dict = {"years":i.get("f5").strip()+'-'+i.get("f6").strip(), "filter_code": clean(i.get("f7").strip()), "f8": i.get("f8").strip()}
            img_info[cid_kid][kid_A]['extra_info'].append(_dict)
            if img not in img_info[cid_kid][kid_A]['img']:
                img_info[cid_kid][kid_A]['img'].append(img)
            # if img_info[subgroup]['img'] == '':
            #     img_info[subgroup]['img'] = img
        return img_info

    def get_mid_new_(self, cid, kid_group_code, img_list):
        sql = u'''select distinct * from `illnote` where trim(f2)="{}" and trim(f3)="{}"'''.format(cid.strip(), kid_group_code.strip())
        self.src_curs.execute(sql)
        data = self.src_curs.fetchall()
        subgroup_list = [i.get("f3").strip()+i.get("f4").strip() for i in data]
        img_info = {i: {'extra_info':[], 'img': []} for i in subgroup_list}
        for i in data:
            img = i.get("f9").strip()
            # if img+'.png' not in img_list:
            #     continue
            subgroup = i.get("f3").strip()+i.get("f4").strip()
            _dict = {"years":i.get("f5").strip()+'-'+i.get("f6").strip(), "filter_code": clean(i.get("f7").strip()), "f8": i.get("f8").strip()}
            img_info[subgroup]['extra_info'].append(_dict)
            if img not in img_info[subgroup]['img']:
                img_info[subgroup]['img'].append(img)
            # if img_info[subgroup]['img'] == '':
            #     img_info[subgroup]['img'] = img
        return img_info

    def run_new(self):
        # 获取所有图片的列表
        img_path = '/Users/duanmengdi/Documents/nissan_new/nissan_img'
        img_list = self.get_img_list(img_path)
        # 获取所有cid,
        cid_list = self.get_cid_list()
        all_img_info = self.get_mid_new()
        all_kid_group_info = self.get_kid_group_info()
        # cid_list = ['ZE0E']
        # 遍历cid,获取其所有主组
        for cid in cid_list:
            main_group_dict = self.get_main_group_dict(cid)
            for m in main_group_dict:
                group_code = m  # 主组代号 A,B,C,D...
                group_name = main_group_dict[m]  # 主组名: AXLE & SUSPENSION
                my_group_code = self.num_label[group_name.strip()]
                # 根据cid,主组获取其所有子组,子组名(英文)
                kid_group_info = all_kid_group_info[cid.strip()+group_code.strip()]
                for k in kid_group_info:
                    kid_group_code = k["kid_group_code"]
                    kid_group_name = k["kid_group_name"]
                    kid_group_name_cn = k["kid_group_name_cn"]

                    # 根据cid,子组编号,获取图组info字典
                    # {kid_group_code+img_group:{'extra_info':{'years':'20080500-20090700', 'F7':"CVT", 'F8':"ASSEMBLY & FITTING PARTS"},'img': ''}, ...}
                    img_info = all_img_info[cid.strip()+kid_group_code.strip()]
                    for i in img_info:
                        subgroup = i
                        subgroup_info = img_info[i]
                        extra_info = subgroup_info.get("extra_info")
                        img_code_list = subgroup_info.get("img")
                        # if not img_code:
                        #     with open('error_new.log', 'a') as f:
                        #         f.write("没有img_code,{}_{}".format(cid.strip(), kid_group_code))
                        #         f.write('\n')
                        #     continue
                        # if img_code+'.png' not in img_list:
                        #     with open('error_new.log', 'a') as f:
                        #         f.write("图片不存在: "+cid.strip()+'_'+subgroup)
                        #         f.write('\n')
                        #     continue
                        mid = img_code_list[0] if len(img_code_list)==1 else ','.join(img_code_list)
                        md5str = (cid.strip() + mid).encode("utf8")
                        uid = self.get_md5(md5str)
                        # self.r.sadd("uid_set", uid)
                        _dict = dict(
                            uid=uid,
                            cid=cid.strip(),
                            num=my_group_code,
                            maingroup=my_group_code,
                            subgroup=subgroup,
                            mid=mid,
                            brandCode="nissan",
                            name=kid_group_name_cn.strip(),
                            description='',
                            model='',
                            condition='',
                            imageLarge='',
                            logo='',
                            imgName=mid,
                            extra_info=json.dumps(extra_info),
                            updatetime='now()',
                            createtime='now()'
                        )
                        insert_sql = self.construct_insert_sql(self.insert_table, _dict)
                        # print(insert_sql)
                        # exit()
                        try:
                            self.dest_curs.execute(insert_sql)
                        except Exception as e:
                            print(insert_sql)
                            with open('error_new.log', 'a') as f:
                                f.write("插入失败" + insert_sql)
                                f.write('\n')
                            # raise e
                        self.num += 1
                        if self.num % 1000 == 0:
                            self.dest_conn.commit()
                            print("提交insert缓存, 当前数据量: {}".format(self.num))
        self.dest_conn.commit()
        print("最终提交, 总数据量: {}".format(self.num))

    # def run(self):
    #     # 获取所有图片的列表
    #     img_path = '/Users/duanmengdi/Documents/nissan/nissan_data/Pictures'
    #     img_list = self.get_img_list(img_path)
    #     # 获取所有cid,
    #     cid_list = self.get_cid_list()
    #     # 遍历cid,获取其所有主组
    #     for cid in cid_list:
    #         main_group_dict = self.get_main_group_dict(cid)
    #         for m in main_group_dict:
    #             group_code = m # 主组代号 A,B,C,D...
    #             group_name = main_group_dict[m]  # 主组名: AXLE & SUSPENSION
    #             my_group_code = self.num_label[group_name.strip()]
    #             # 根据cid,主组获取其所有子组,子组名(英文)
    #             kid_group_info = self.get_kid_group_info(cid, group_code)
    #             for k in kid_group_info:
    #                 kid_group_code = k["kid_group_code"]
    #                 kid_group_name = k["kid_group_name"]
    #
    #                 # 根据cid,子组,获取图组编号,图片编号(备用),
    #                 img_info = self.get_mid(cid, kid_group_code)
    #                 for i in img_info:
    #                     img_group = i.get("img_group")  # 图组编号
    #                     img_code = i.get("img_code")  # 图片编号
    #                     if not img_code:
    #                         with open('error.log', 'a') as f:
    #                             f.write("没有img_code,{}_{}_{}".format(cid.strip(), kid_group_code, img_group))
    #                             f.write('\n')
    #                         continue
    #                     if img_code+'.png'  not in img_list:
    #                         with open('error.log', 'a') as f:
    #                             f.write("图片不存在: "+cid.strip()+'_'+kid_group_code+'_'+img_group)
    #                             f.write('\n')
    #                         continue
    #
    #                     mid = cid.strip()+'_'+kid_group_code+'_'+img_group
    #                     md5str = (cid+mid).encode("utf8")
    #                     uid = self.get_md5(md5str)
    #                     # self.r.sadd("uid_set", uid)
    #                     _dict = dict(
    #                         uid=uid,
    #                         cid=cid.strip(),
    #                         num=my_group_code,
    #                         maingroup=my_group_code,
    #                         subgroup="",
    #                         mid=mid,
    #                         brandCode="nissan",
    #                         name=kid_group_name.strip(),
    #                         imgName=img_code,
    #                         extra_info='',
    #                         updatetime='now()',
    #                         createtime='now()'
    #                     )
    #                     # print(_dict)
    #                     insert_sql = self.construct_insert_sql(self.insert_table, _dict)
    #                     # print(insert_sql)
    #                 # exit()
    #                     try:
    #                         self.dest_curs.execute(insert_sql)
    #                     except Exception as e:
    #                         print(insert_sql)
    #                         with open('error.log', 'a') as f:
    #                             f.write("插入失败"+insert_sql)
    #                             f.write('\n')
    #                         # raise e
    #                     self.num += 1
    #                     if self.num % 1000 == 0:
    #                         self.dest_conn.commit()
    #                         print("提交insert缓存, 当前数据量: {}".format(self.num))
    #     self.dest_conn.commit()
    #     print("最终提交, 总数据量: {}".format(self.num))


if __name__ == '__main__':
    src_db = dict(
        host="127.0.0.1",
        port=3306,
        user="root",
        passwd="123456",
        db="nissan_new",
        charset="utf8",
    )
    dest_db = dict(
        host="127.0.0.1",
        port=3306,
        user="root",
        passwd="123456",
        db="nissan_new",
        charset="utf8",
    )
    insert_table = 'cars_structure_collection'
    tools = GetCarsStructureCollection(src_db=src_db, dest_db=dest_db, insert_table=insert_table)
    # tools.run()
    tools.run_new()
